/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.login;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Martin Spencer Sy
 */
public class AccountManager {
    private static Connection con = null;
    private static Statement s = null;
    private static String sql = null;
    private static ResultSet rs = null;
    
    public static ResultSet getLockedCustomers(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            
            sql = "CALL getLockedUsers()";
            rs = s.executeQuery(sql);
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static ResultSet getLockedFinanceManagers(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            sql = "CALL getLockedManagers(0, 1)";
            rs = s.executeQuery(sql);
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static ResultSet getLockedProductManagers(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            
            sql = "CALL getLockedManagers(1, 0)";
            rs = s.executeQuery(sql);
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static int addManager(String username, String password, String type){
        
        Connection con = null;
        PreparedStatement ps;
        int insertResult;
            
        try {
            
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "CALL checkUsername(?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();
            
            if ( !(rs.next()) ) {//if username is not yet taken
                
                if( type.equals("finance") ){
                    sql = "CALL createFinanceManager(?, ?)";
                }
                else if( type.equals("product") ){
                    sql = "CALL createProductManager(?, ?)";
                }

                System.out.println(type);

                ps = con.prepareStatement(sql);

                ps.setString(1, username);
                ps.setString(2, password);

                //execute the instert statement
                insertResult = ps.executeUpdate();

                //check if the user was successfully added
                if( insertResult == 1 ){
                    closeDB();
                    return 1;
                }
                else{
                    closeDB();
                    return -1;
                }
                
            } 
            else {
                return 0;
            }
        } 
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return -1;
    }
    
    public static boolean unlockAccount(String user){
        
        PreparedStatement ps;
        int updateResult;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT isProductManager, isFinanceManager FROM listUserAccess WHERE uid = (SELECT uid FROM user WHERE username=?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, user);
            rs = ps.executeQuery();
            rs.next();
            
            if( rs.getInt(1) >= 1 || rs.getInt(2) == 1){
                sql = "CALL unlockManagerAcct(?)";
                ps = con.prepareStatement(sql);
                ps.setString(1, user);
                updateResult = ps.executeUpdate();

                if(updateResult == 1){
                    return true;
                }
                else{
                    return false;
                }
            }
            else{
                sql = "CALL unlockUserAcct(?)";
                ps = con.prepareStatement(sql);
                ps.setString(1, user);
                updateResult = ps.executeUpdate();

                if(updateResult == 1){
                    return true;
                }
                else{
                    return false;
                }
            }
            
//            sql = "CALL unlockUserAcct(?)";
//            
//            ps = con.prepareStatement(sql);
//            
//            ps.setString(1, user);
//            
//            updateResult = ps.executeUpdate();
//            
//            if(updateResult == 1){
//                return true;
//            }
//            else{
//                return false;
//            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return false;
    }
    
    public static void closeDB(){
        try {
            con.close();
        } 
        catch (SQLException ex) {
            //Logger.getLogger(ProductManager.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(AccountManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
    }
}
